import tkinter as tk
import pymysql
import tkinter.messagebox

conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='123456',
    db='db_library',
    port=3306,
    charset='utf8'
)
cur = conn.cursor()

root = tk.Tk()
root.title('Management SQL')
root.geometry('600x600')

tk.Label(root,text='NANE').place(x=250,y=40)
tk.Label(root,text='AGR').place(x=385,y=40)
tk.Label(root,text='GENDER').place(x=500,y=40)

var_I_1 = tk.StringVar()
var_I_2 = tk.StringVar()
var_I_3 = tk.StringVar()
Increase1 = tk.Entry(root,textvariable=var_I_1,width=10)
Increase1.place(x=235,y=105)
Increase2 = tk.Entry(root,textvariable=var_I_2,width=10)
Increase2.place(x=365,y=105)
Increase3 = tk.Entry(root,textvariable=var_I_3,width=10)
Increase3.place(x=495,y=105)

def Increase_record():
    var_I_11 = var_I_1.get()
    var_I_22 = var_I_2.get()
    var_I_33 = var_I_3.get()
    if var_I_22.isdigit():
        if var_I_33 == '男' or var_I_33 == '女':
            try:
                w = "INSERT INTO test(name,age,gender)VALUES (%s,%s,%s)"
                values = (var_I_11,var_I_22,var_I_33)
                cur.execute(w, values)
                conn.commit()

                tk.messagebox.showinfo(message='添加成功')
            except:
                tk.messagebox.showerror(title='数据重复', message=var_I_11 + '已经存在')
        else:
            tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
    else:
        tk.messagebox.showerror(title='无效数据',message='age请输入数字')

Increase = tk.Button(root,text='Increase record',width=15,command=Increase_record)
Increase.place(x=30,y=100)

var_D_1 = tk.StringVar()
var_D_2 = tk.StringVar()
var_D_3 = tk.StringVar()

Delete1 = tk.Entry(root,textvariable=var_D_1,width=10)
Delete1.place(x=235,y=235)

Delete2 = tk.Entry(root,textvariable=var_D_2,width=10)
Delete2.place(x=365,y=235)

Delete3 = tk.Entry(root,textvariable=var_D_3,width=10)
Delete3.place(x=495,y=235)

def Delete_record():
    var_D_11 = var_D_1.get()
    var_D_22 = var_D_2.get()
    var_D_33 = var_D_3.get()

    if var_D_11:
        if var_D_22 and var_D_33:
            w_2 = "SELECT name  FROM test WHERE NAME=(%s) and (age=(%s) and gender=(%s))"
            values = (var_D_11,var_D_22,var_D_33)
            cur.execute(w_2, values)
            ret = cur.fetchall()  # 获取数据
            if ret:
                w_2 = "DELETE FROM test WHERE NAME=(%s) and (age=(%s) and gender=(%s))"
                values = (var_D_11,var_D_22,var_D_33)
                cur.execute(w_2, values)
                conn.commit()
                tk.messagebox.showinfo(message='删除成功')
            else:
                tk.messagebox.showerror(title='无效数据',message='无记录')
                conn.commit()
    elif var_D_22:
        if var_D_33:
            w_2 = "SELECT name FROM test WHERE age=(%s) and gender=(%s)"
            values = (var_D_22,var_D_33)
            cur.execute(w_2, values)
            ret_2 = cur.fetchall()  # 获取数据
            if ret_2:
                w_2 = "DELETE FROM test WHERE age=(%s) and gender = (%s)"
                values = (var_D_22,var_D_33)
                cur.execute(w_2, values)
                conn.commit()
                tk.messagebox.showinfo(message='删除成功')
            else:
                tk.messagebox.showerror(title='无效数据', message='无记录')
                conn.commit()
        else:
            w_2 = "SELECT name FROM test WHERE age=(%s)"
            values = (var_D_22)
            cur.execute(w_2, values)
            ret_2 = cur.fetchall()  # 获取数据
            if ret_2:
                w_2 = "DELETE FROM test WHERE age=(%s)"
                values = (var_D_22)
                cur.execute(w_2, values)
                conn.commit()
                tk.messagebox.showinfo(message='删除成功')
            else:
                tk.messagebox.showerror(title='无效数据', message='无记录')
                conn.commit()
    else:
        w_2 = "SELECT name FROM test WHERE gender=(%s)"
        values = (var_D_33)
        cur.execute(w_2, values)
        ret_2 = cur.fetchall()  # 获取数据
        if ret_2:
            w_2 = "DELETE FROM test WHERE gender=(%s)"
            values = (var_D_33)
            cur.execute(w_2, values)
            conn.commit()
            tk.messagebox.showinfo(message='删除成功')
        else:
            tk.messagebox.showerror(title='无效数据', message='数据不纯在')
            conn.commit()

Delete = tk.Button(root,text='Delete record',width=15,command=Delete_record)
Delete.place(x=30,y=230)

var_S_1 = tk.StringVar()
var_S_2 = tk.StringVar()
var_S_3 = tk.StringVar()
Search1 = tk.Entry(root,textvariable=var_S_1,width=10)
Search1.place(x=235,y=365)
Search2 = tk.Entry(root,textvariable=var_S_2,width=10)
Search2.place(x=365,y=365)
Search3 = tk.Entry(root,textvariable=var_S_3,width=10)
Search3.place(x=495,y=365)

def Search_record():
    var_S_11 = var_S_1.get()
    var_S_22 = var_S_2.get()
    var_S_33 = var_S_3.get()
    if var_S_11:
        if var_S_22 :
            if var_S_22.isdigit():
                if var_S_33 :
                    if var_S_33 == '男' or var_S_33 == '女':
                        w_2 = "SELECT name,age,gender FROM test WHERE name like (%s) and age=(%s) and gender = (%s)"
                        values = (var_S_11 + '%',var_S_22,var_S_33)
                        cur.execute(w_2, values)
                        ret = cur.fetchall()  # 获取数据
                        if ret:
                            root_2 = tk.Toplevel(root)
                            root_2.geometry('300x300')
                            tk.Label(root_2, text='NAME:').place(x=40, y=20)
                            tk.Label(root_2, text='AGE:').place(x=130, y=20)
                            tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                            s_1 = tk.Scrollbar(root_2)
                            s_1.pack(side=tk.RIGHT, fill=tk.Y)

                            listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                            for i in ret:
                                listbox_1.insert('end', str(i))

                            listbox_1.place(x=40, y=60, width=220, height=230)
                            s_1.config(command=listbox_1.yview)
                            conn.commit()
                        else:
                            tk.messagebox.showerror(title='无效数据', message='无记录')
                            conn.commit()
                    else:
                        tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
                else:
                    if var_S_22.isdigit():
                        w_2 = "SELECT name,age,gender FROM test WHERE name like (%s) and age=(%s)"
                        values = (var_S_11 + '%', var_S_22)
                        cur.execute(w_2, values)
                        ret = cur.fetchall()  # 获取数据
                        if ret:
                            root_2 = tk.Toplevel(root)
                            root_2.geometry('300x300')
                            tk.Label(root_2, text='NAME:').place(x=40, y=20)
                            tk.Label(root_2, text='AGE:').place(x=130, y=20)
                            tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                            s_1 = tk.Scrollbar(root_2)
                            s_1.pack(side=tk.RIGHT, fill=tk.Y)

                            listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                            for i in ret:
                                listbox_1.insert('end', str(i))

                            listbox_1.place(x=40, y=60, width=220, height=230)
                            s_1.config(command=listbox_1.yview)
                            conn.commit()
                        else:
                            tk.messagebox.showerror(title='无效数据', message='无记录')
                            conn.commit()
                    else:
                        tk.messagebox.showerror(title='无效数据', message='age请输入数字')
            else:
                tk.messagebox.showerror(title='无效数据', message='age请输入数字')
        elif var_S_33:
            if var_S_33 == '男' or var_S_33 == '女':
                w_2 = "SELECT name,age,gender FROM test WHERE name like (%s) and gender = (%s)"
                values = (var_S_11 + '%',var_S_33)
                cur.execute(w_2, values)
                ret = cur.fetchall()  # 获取数据
                if ret:
                    root_2 = tk.Toplevel(root)
                    root_2.geometry('300x300')
                    tk.Label(root_2, text='NAME:').place(x=40, y=20)
                    tk.Label(root_2, text='AGE:').place(x=130, y=20)
                    tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                    s_1 = tk.Scrollbar(root_2)
                    s_1.pack(side=tk.RIGHT, fill=tk.Y)

                    listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                    for i in ret:
                        listbox_1.insert('end', str(i))

                    listbox_1.place(x=40, y=60, width=220, height=230)
                    s_1.config(command=listbox_1.yview)
                    conn.commit()
                else:
                    tk.messagebox.showerror(title='无效数据', message='无记录')
                    conn.commit()
            else:
                tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
        else:
            w_2 = "SELECT name,age,gender FROM test WHERE name like (%s)"
            values = (var_S_11 + '%')
            cur.execute(w_2, values)
            ret = cur.fetchall()  # 获取数据
            if ret:
                root_2 = tk.Toplevel(root)
                root_2.geometry('300x300')
                tk.Label(root_2, text='NAME:').place(x=40, y=20)
                tk.Label(root_2, text='AGE:').place(x=130, y=20)
                tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                s_1 = tk.Scrollbar(root_2)
                s_1.pack(side=tk.RIGHT, fill=tk.Y)

                listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                for i in ret:
                    listbox_1.insert('end', str(i))

                listbox_1.place(x=40, y=60, width=220, height=230)
                s_1.config(command=listbox_1.yview)
                conn.commit()
            else:
                tk.messagebox.showerror(title='无效数据', message='无记录')
                conn.commit()
    elif var_S_22:
        if var_S_22.isdigit():
            if var_S_33:
                if var_S_33 == '男' or var_S_33 == '女':
                    w_2 = "SELECT name,age,gender FROM test WHERE age=(%s) and gender = (%s)"
                    values = (var_S_22,var_S_33)
                    cur.execute(w_2, values)
                    ret = cur.fetchall()  # 获取数据
                    if ret:
                        root_2 = tk.Toplevel(root)
                        root_2.geometry('300x300')
                        tk.Label(root_2, text='NAME:').place(x=40, y=20)
                        tk.Label(root_2, text='AGE:').place(x=130, y=20)
                        tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                        s_1 = tk.Scrollbar(root_2)
                        s_1.pack(side=tk.RIGHT, fill=tk.Y)

                        listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                        for i in ret:
                            listbox_1.insert('end', str(i))

                        listbox_1.place(x=40, y=60, width=220, height=230)
                        s_1.config(command=listbox_1.yview)
                        conn.commit()
                    else:
                        tk.messagebox.showerror(title='无效数据', message='无记录')
                        conn.commit()
                else:
                    tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')

            else:
                w_2 = "SELECT name,age,gender FROM test WHERE age=(%s)"
                values = (var_S_22)
                cur.execute(w_2, values)
                ret = cur.fetchall()  # 获取数据
                if ret:
                    root_2 = tk.Toplevel(root)
                    root_2.geometry('300x300')
                    tk.Label(root_2, text='NAME:').place(x=40, y=20)
                    tk.Label(root_2, text='AGE:').place(x=130, y=20)
                    tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                    s_1 = tk.Scrollbar(root_2)
                    s_1.pack(side=tk.RIGHT, fill=tk.Y)

                    listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                    for i in ret:
                        listbox_1.insert('end', str(i))

                    listbox_1.place(x=40, y=60, width=220, height=230)
                    s_1.config(command=listbox_1.yview)
                    conn.commit()
                else:
                    tk.messagebox.showerror(title='无效数据', message='无记录')
                    conn.commit()
        else:
            tk.messagebox.showerror(title='无效数据', message='age请输入数字')
    elif var_S_33:
        if var_S_33 == '男' or var_S_33 == '女':
            w_2 = "SELECT name,age,gender FROM test WHERE gender = (%s)"
            values = (var_S_33)
            cur.execute(w_2, values)
            ret = cur.fetchall()  # 获取数据
            if ret:
                root_2 = tk.Toplevel(root)
                root_2.geometry('300x300')
                tk.Label(root_2, text='NAME:').place(x=40, y=20)
                tk.Label(root_2, text='AGE:').place(x=130, y=20)
                tk.Label(root_2, text='GENDER:').place(x=200, y=20)

                s_1 = tk.Scrollbar(root_2)
                s_1.pack(side=tk.RIGHT, fill=tk.Y)

                listbox_1 = tk.Listbox(root_2, yscrollcommand=s_1.set)
                for i in ret:
                    listbox_1.insert('end', str(i))

                listbox_1.place(x=40, y=60, width=220, height=230)
                s_1.config(command=listbox_1.yview)
                conn.commit()
            else:
                tk.messagebox.showerror(title='无效数据', message='无记录')
                conn.commit()
        else:
            tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
    else:
        tk.messagebox.showerror(title='无效数据',message='请输入数字')
        conn.commit()

Search = tk.Button(root,text='Search record',width=15,command=Search_record)
Search.place(x=30,y=360)

var_M_1 = tk.StringVar()
var_M_2 = tk.StringVar()
var_M_3 = tk.StringVar()
var_M_4 = tk.StringVar()
var_M_5 = tk.StringVar()
var_M_6 = tk.StringVar()
Modify1 = tk.Entry(root,textvariable=var_M_1,width=10)
Modify1.place(x=235,y=495)
Modify2 = tk.Entry(root,textvariable=var_M_2,width=10)
Modify2.place(x=365,y=495)
Modify3 = tk.Entry(root,textvariable=var_M_3,width=10)
Modify3.place(x=495,y=495)

def Modify_record():
    var_M_11 = var_M_1.get()
    var_M_22 = var_M_2.get()
    var_M_33 = var_M_3.get()
    if var_M_22.isdigit():
        if var_M_33 == '男' or var_M_33 == '女':
            w_2 = "SELECT name FROM test WHERE name=(%s) and age=(%s) and gender = (%s)"
            values = (var_M_11,var_M_22,var_M_33)
            cur.execute(w_2, values)
            ret = cur.fetchall()  # 获取数据
            if ret:
                mess_1 = tk.messagebox.askyesno(title='are you sure', message='确定将' + '  ' + var_M_11 + '  ' + '进行修改吗？')
                if mess_1:
                    root_3 = tk.Toplevel(root)
                    root_3.geometry('300x300')
                    tk.Label(root_3, text='NAME:').place(x=40, y=20)
                    tk.Label(root_3, text='AGE:').place(x=130, y=20)
                    tk.Label(root_3, text='GENDER:').place(x=200, y=20)
                    tk.Label(root_3, text='修改为:').place(x=40, y=100)

                    var_M_4.set(var_M_11)
                    var_M_5.set(var_M_22)
                    var_M_6.set(var_M_33)
                    listbox_2 = tk.Listbox(root_3,listvariable=var_M_4)
                    listbox_2.place(x=40,y=40,width=70,height=20)
                    listbox_2 = tk.Listbox(root_3, listvariable=var_M_5)
                    listbox_2.place(x=130, y=40, width=60, height=20)
                    listbox_2 = tk.Listbox(root_3, listvariable=var_M_6)
                    listbox_2.place(x=205, y=40, width=70, height=20)

                    e_1 = tk.Entry(root_3, show='')
                    e_1.place(x=40, y=130,width=70,height=20)
                    e_2 = tk.Entry(root_3, show='')
                    e_2.place(x=130, y=130, width=60, height=20)
                    e_3 = tk.Entry(root_3, show='')
                    e_3.place(x=205, y=130, width=70, height=20)
                    def Modify_record_1():
                        mess_2 = tk.messagebox.askyesno(title='are you sure',message='确定将' + '  ' + var_M_11 + '  ' + '进行修改吗？')
                        if mess_2:
                            Modify_record_2()
                    def Modify_record_2():
                        var_e_1 = e_1.get()
                        var_e_2 = e_2.get()
                        var_e_3 = e_3.get()
                        if var_e_2.isdigit():
                            if var_e_3 == '男' or var_e_3 == '女':

                                w_3 = "UPDATE test SET name = (%s), age = (%s) ,gender = (%s) WHERE  name = (%s)"
                                values = (var_e_1, var_e_2, var_e_3, var_e_1)
                                cur.execute(w_3, values)
                                conn.commit()
                                tk.messagebox.showinfo(title='ok',message='修改成功')
                            else:
                                tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
                        else:
                            tk.messagebox.showerror(title='无效数据', message=' AGE 请输入数字')

                    tk.Button(root_3,text='修改',command=Modify_record_1).place(x=205, y=170,width=70,heigh=30)
            else:
                tk.messagebox.showerror(title='无记录', message='数据不存在')
        else:
            tk.messagebox.showerror(title='无效数据', message='grnder请输入‘男’或‘女’')
    else:
        tk.messagebox.showerror(title='无效数据',message=' AGE 请输入数字')

Modify = tk.Button(root,text='Modify record',width=15,command=Modify_record)
Modify.place(x=30,y=490)

root.mainloop()